import pandas as pd

def process_excel(input_file, output_file):
    # Read all sheets from the Excel file
    excel_file = pd.ExcelFile(input_file)
    sheet_names = excel_file.sheet_names
    
    # Create empty DataFrames for both worksheets
    area_df = pd.DataFrame()
    amount_df = pd.DataFrame()
    
    # Get the first column from row 6 to 6th to last row
    first_sheet = pd.read_excel(input_file, sheet_name=sheet_names[0])
    filenames = first_sheet.iloc[5:-6, 0]
    
    # Add Filename column to both DataFrames
    area_df['Filename'] = filenames.reset_index(drop=True)
    amount_df['Filename'] = filenames.reset_index(drop=True)
    
    # Process each sheet except the last two
    for sheet_name in sheet_names[:-2]:
        # Read the sheet
        df = pd.read_excel(input_file, sheet_name=sheet_name)
        
        # Extract the fifth column (Area) 
        area_data = df.iloc[5:-6, 4]  
        
        # Extract the ninth column (Amount)
        amount_data = df.iloc[5:-6, 8]  
        
        # Add the columns to respective DataFrames with sheet name as column name
        area_df[sheet_name] = area_data.reset_index(drop=True)
        amount_df[sheet_name] = amount_data.reset_index(drop=True)
    
    # Create sheet names and corresponding DataFrames
    sheets = {
        'Area': area_df,
        'Amount': amount_df
    }
    
    # Save to output file 
    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        for sheet_name, df in sheets.items():
            df.to_excel(writer, sheet_name=sheet_name, index=False)


input_file = "HB1多批次含测数据_Short.xlsx"
output_file = "output_file.xlsx"
process_excel(input_file, output_file)